<?php

/**
 * 课程表格式化工具：
 * 1. 对比调课后的新Excel和原始Excel的区别，将调课的老师、课程标红
 * 2. 合并“老师”和“课程”两行作为新的一行，因为习惯这样看了
 */

error_reporting(E_ALL & ~ E_WARNING);

require_once './libraries/PHPExcel.php';

class CourseFormatter
{
	// public $input_filepath = NULL;
	public $input_filepath = array(
		'week' => '17',
		'source_excel' => 'F:\小灰\2023\调课例子/总表表模 2023.11.14  20.27.xls',
		'new_excel' => 'F:\小灰\2023\调课例子/总课程表 (横行格式)2023.11.26  18.5.xls',
	);

	public $saved_file_name = NULL;
	public $output_to_web = FALSE;
	public $extension = 'xls';

	private $_teacher_course_start_index = 4; // excel 左边的第几行
	private $_teacher_course_start_column = 'D'; // excel 上面的第几列

	private $_max_index = NULL;
	private $_max_column = NULL;

	private $_week_index = 2; // excel 左边的第几行
	private $_week_column = 'A'; // excel 上面的第几列
	public $week_text_template = '美景学校第{week}周课程表';

	public $remove_indexs = array(1); // excel 左边的第几行

	private $_cell_name = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ');

	public $diff_list = array();

	function __construct($input_filepath = NULL)
	{
		$this->_init($input_filepath);
	}

	private function _init($input_filepath) {
		if (!empty($input_filepath)) {
			$this->input_filepath = $input_filepath;
		}
	}

	public function run() {
		$this->_logger('Reading new excel...');
		$new_data = $this->_read_excel_file($this->input_filepath['new_excel']);
		if (empty($new_data)) {
			die('New excel data is empty, please check file: ' . $this->input_filepath['new_excel']);
		}

		$this->_logger('Reading source excel...');
		$source_data = $this->_read_excel_file($this->input_filepath['source_excel']);
		if (empty($new_data)) {
			die('Source excel data is empty, please check file: ' . $this->input_filepath['source_excel']);
		}

		$this->_logger('Comparing new with source excel...');
		$this->compare_new_with_source($new_data, $source_data);

		$this->_logger('Merging course name and teacher for new excel...');
		$this->merge_course_name_and_teacher($new_data);
	}

	public function compare_new_with_source(&$new_data, &$source_data) {
		$diff_list = array();

		foreach ($new_data as $index => $row_data) {
			$this->_max_index = $index > $this->_max_index ? $index : $this->_max_index;

			if ($index >= $this->_teacher_course_start_index) {
				if (!empty($row_data)) {
					foreach ($row_data as $column => $v) {
						$this->_max_column = array_search($column, $this->_cell_name) > array_search($this->_max_column, $this->_cell_name) ? $column : $this->_max_column;

						if (array_search($column, $this->_cell_name) >= array_search($this->_teacher_course_start_column, $this->_cell_name) && !empty($source_data[$index]) && !empty($source_data[$index][$column]) && $source_data[$index][$column]!=$v) {
							$diff_index = $this->_get_diff_index($index);

							$diff_list[$diff_index][$column] = $v;
						}
					}
				}
			}
		}
		
		$this->diff_list = $diff_list;
	}

	private function _get_diff_index($index) {
		if ($index % 2 === 0) {
			return $index;
		} else {
			return $index - 1;
		}
	}

	public function merge_course_name_and_teacher(&$new_data) {
		$is_new_file  = FALSE;

    	if ($is_new_file) {
			$objPHPExcel = new PHPExcel();
		} else {
			$full_filename = $this->input_filepath['source_excel'];
			try {
	            $inputFileType = \PHPExcel_IOFactory::identify($full_filename);
	            $objReader = \PHPExcel_IOFactory::createReader($inputFileType);
	            $objPHPExcel = $objReader->load($full_filename);
	        } catch(\Exception $e) {
	            die('Failed to load excel file: "' . pathinfo($full_filename,PATHINFO_BASENAME) . '": ' . $e->getMessage());
	        }
		}

		$objActSheet = $objPHPExcel->getActiveSheet();
		$this->_logger("Done getActiveSheet for excel" . ', memory: ' . $this->memory_usage());

		// 显示“周”
		$week = !empty($this->input_filepath['week']) ? $this->input_filepath['week'] : '';
		if (!empty($week)) {
			$week_word = str_replace('{week}', $week, $this->week_text_template);
			$objActSheet->setCellValue("{$this->_week_column}{$this->_week_index}", $week_word);
		}

		// 合并“课程和老师”
		for ($i = $this->_teacher_course_start_index; $i <= $this->_max_index; $i++) {
			if ($i % 2 ===0) {
				for ($j = array_search($this->_teacher_course_start_column, $this->_cell_name); $j <= array_search($this->_max_column, $this->_cell_name); $j++) {
					// 合并单元格
					$start_merge_i = $i;
					$start_merge_j = $this->_cell_name[$j];
					$end_merge_i = $i+1;
					$end_merge_j = $this->_cell_name[$j];
					$merge_value = $new_data[$start_merge_i][$start_merge_j] . "\n" . $new_data[$end_merge_i][$end_merge_j];

					$objActSheet->mergeCells("{$start_merge_j}{$start_merge_i}:{$end_merge_j}{$end_merge_i}"); // $objActSheet->mergeCells("A1:A2");
					$objActSheet->setCellValue("{$start_merge_j}{$start_merge_i}", $merge_value);
					$objActSheet->getStyle("{$start_merge_j}{$start_merge_i}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
					$objActSheet->getStyle("{$start_merge_j}{$start_merge_i}")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
					$objActSheet->getStyle("{$start_merge_j}{$start_merge_i}")->getAlignment()->setWrapText(TRUE);
					$objActSheet->getColumnDimension("{$start_merge_j}")->setWidth(mb_strlen($merge_value) * 1.5);
					// $objActSheet->getColumnDimension("{$start_merge_j}")->setAutoSize(TRUE);
					
					// 不同的老师、课程标红：set red for diff_list
					if (!empty($this->diff_list[$i]) && !empty($this->diff_list[$i][$start_merge_j])) {
						$objActSheet->getStyle("{$start_merge_j}{$start_merge_i}")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
					}
				}
			}
		}

		// Remove Rows
		if (!empty($this->remove_indexs)) {
			foreach ($this->remove_indexs as $v) {
				$objActSheet->removeRow($v, 1);
			}
		}
		
		$p = pathinfo($this->input_filepath['new_excel']);
		$saved_file_name = !empty($this->saved_file_name) ? $this->saved_file_name : $p['filename'];
		$filename = $saved_file_name . '_' . Date('Ymd_His') . '.' . $this->extension;
		if (!$this->output_to_web) {
			$save_filename = $p['dirname'] . '/' . $filename;

			// // Excel2007
			// // $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
			
			// // Excel 2003
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

			$objWriter->save($save_filename);

			$this->_logger("Saved excel to path: {$save_filename}");
		} else {
			// 直接输出Excel文件给浏览器下载
			// // header('Content-Type: application/vnd.ms-excel');
			// header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
			// header('Content-Disposition: attachment;filename="myfile.xlsx"');
			// header('Cache-Control: max-age=0');
			// $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
			// $objWriter->save('php://output');
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
			// header("Content-Type: application/vnd.ms-excel");
			header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
			header("Content-Disposition: attachment;filename=" . $filename);
			header('Cache-Control: max-age=0');
			$objWriter->save('php://output');
	    }

		// free up memory
		// $objPHPExcel->disconnectWorksheets();
		// $objPHPExcel->garbageCollect();
		// unset($objPHPExcel);
	}

	/** 
	*  读取excel文件
	* @param string $file excel文件
	* @param string $sheet
	* @return string   返回解析数据
	* @throws PHPExcel_Exception
	* @throws PHPExcel_Reader_Exception
	*/ 
	function _read_excel_file($file='', $sheet=0, $start_row_index = 0, $page_size = NULL) {
		$file = iconv("utf-8", "gb2312", $file);   //转码

		if (empty($file) OR !file_exists($file)) {
			die('file not exists!');
		}

		// include('PHPExcel.php');  //引入PHP EXCEL类

		$objRead = new PHPExcel_Reader_Excel2007(); // new PHPExcel_Reader_Excel2007();

		if(!$objRead->canRead($file)){
			$objRead = new PHPExcel_Reader_Excel5();
			if(!$objRead->canRead($file)){
				die('No Excel!');
			}
		}

		$cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ');

		$obj = $objRead->load($file);
		$currSheet = $obj->getSheet($sheet);
		$columnH = $currSheet->getHighestColumn();
		$columnCnt = array_search($columnH, $cellName);
		$rowCnt = $currSheet->getHighestRow();

		$endCnt = empty($page_size) ? $rowCnt : $start_row_index+$page_size;
		$endCnt = $endCnt > $rowCnt ? $rowCnt : $endCnt;

		$data = array();
		for($_row=$start_row_index+1; $_row<=$endCnt; $_row++) {
			for($_column=0; $_column<=$columnCnt; $_column++) {
				$cellId = $cellName[$_column].$_row;
				$cellValue = $currSheet->getCell($cellId)->getValue();
				//$cellValue = $currSheet->getCell($cellId)->getCalculatedValue();
				if($cellValue instanceof PHPExcel_RichText) {
					$cellValue = $cellValue->__toString();
				}
				$data[$_row][$cellName[$_column]] = $cellValue;
			}
		}

		return $data;
	}

	public function _logger($msg) {
		if (!$this->output_to_web) {
			echo $msg . "\r\n";
		}
	}
	
	function memory_usage() {
		$memory = ( ! function_exists('memory_get_usage')) ? '0' : round(memory_get_usage()/1024/1024, 2).'MB';
		return $memory; 
	}
}

// $c = new CourseFormatter();
// $c->run();

// $input_filepath = array(
// 	'week' => '17',
// 	'source_excel' => 'E:\ruanjian\wamp\www\test\调课例子\1原始模板\Vinsion总课程表 (纵列格式)2023.12.11  17.7.xls',
// 	'new_excel' => 'E:\ruanjian\wamp\www\test\调课例子\2.0总课程表 (纵列格式)2023.12.10  21.23.xls',
// );
// $c = new CourseFormatter($input_filepath);
// $c->run();

